﻿using System;
using System.Data.SQLite;
using S7PlcDataCollector.Database.SQLite;

namespace S7PlcDataCollector.Service.SQLiteProxy
{
    // ReSharper disable once IdentifierTypo
    internal class SqliteHelper
    {
        public string LastErrorMsg { get; private set; }

        public const string TableS7PlcEntity = "S7PlcEntity"; //PLC实体表
        public const string TableS7PlcVariableEntity = "S7PlcVariableEntity"; //PLC变量表

        private static string DataBaseFile => "System.Data.dll";
        private static string ConnectionString => $"Data Source={DataBaseFile};Version=3;Password=";

        #region 初始化数据库

        public bool InitDatabase()
        {
            SqliteDbHelper.ConnectionString = ConnectionString;

            var ret = CreateDefaultDatabase();
            ret = ret && CreateTable();
            return ret;
        }

        //创建数据库
        private bool CreateDefaultDatabase()
        {
            var ret = false;
            try
            {
                using (var conn = new SQLiteConnection(ConnectionString))
                {
                    conn.Open();
                    conn.Close();
                    ret = true;
                }
            }
            catch (Exception e)
            {
                LastErrorMsg = e.Message;
            }

            return ret;
        }

        //创建表
        private bool CreateTable()
        {
            var connectionString = ConnectionString;

            //PLC实体表
            var sql = $"create table if not exists {TableS7PlcEntity}(" +
                      "Rid integer primary key autoincrement, " + //Id（自增）
                      "Type integer not null, " + //PLC型号
                      "Ip text not null, " + //IP地址
                      "Port integer not null, " + //端口
                      "Rack integer not null, " + //RACK
                      "Slot integer not null);"; //SLOT
            var ret = SqliteDbHelper.ExecuteNonQuery(connectionString, sql);

            //PLC变量表
            sql = $"create table if not exists {TableS7PlcVariableEntity}(" +
                  "Rid integer primary key autoincrement, " + //Id（自增）
                  "S7PlcEntityRid integer not null, " + //PLC ID
                  "Variable text not null, " + //变量名称
                  "VariableType integer not null, " + //变量类型
                  "Priority integer not null, " + //优先级
                  "Remark text, " + //备注
                  $"foreign key(S7PlcEntityRid) references {TableS7PlcEntity}(Rid) " + //外键声明
                  " on delete cascade);"; //外键约束：级联删除
            ret = ret && SqliteDbHelper.ExecuteNonQuery(connectionString, sql);

            if (!ret)
            {
                LastErrorMsg = "创建数据库表失败";
            }

            return ret;
        }

        #endregion
    }
}
